﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ApplicationGenerator.DataAccess
{
    public class DesignerDataBaseManager
    {

        #region Variables

        private IDataBaseCommands commandStrategy = null;
        private IDataBaseCommands formCommands = new FormDataBaseCommands();
        private IDataBaseCommands listCommands = new ListDataBaseCommands();
        private IDataBaseCommands workflowCommands = new WorkflowDataBaseCommands();
        private IDataBaseCommands menuCommands = new MenuDataBaseCommands();
        private static volatile DesignerDataBaseManager instance;
        private static object syncObj = new Object();
        public enum ConnectionStringType { Creation, Load };

        private SqlConnection connection = null;
        private string[] ConnectionStrings = new string[2];
        private string _ConnectionString = null;
        private ConnectionStringType connectionType = ConnectionStringType.Creation;

        #endregion

        #region Sql Commands

        private const string CreateDatabaseCommand = "CREATE DATABASE ";
        private const string UseDatabaseCommand = "USE ";
        private const string CreateFormsTableSchema = "CREATE TABLE AG_FORMS(" +
                                                        "[AG_ID] [uniqueidentifier] NOT NULL," +
                                                        "[AG_NAME] [nvarchar](50) NOT NULL UNIQUE NONCLUSTERED," +
                                                        "[AG_BINARY_DATA] [varbinary](max) NOT NULL" +
                                                        ")";
        private const string CreateMenuTableSchema = "CREATE TABLE AG_MENU(" +
                                                        "[AG_ID] [uniqueidentifier] NOT NULL," +
                                                        "[AG_NAME] [nvarchar](50) NOT NULL UNIQUE NONCLUSTERED," +
                                                        "[AG_BINARY_DATA] [varbinary](max) NOT NULL" +
                                                        ")";
        private const string CreateListsTableSchema = "CREATE TABLE AG_LISTS(" +
                                                        "[AG_ID] [uniqueidentifier] NOT NULL," +
                                                        "[AG_NAME] [nvarchar](50) NOT NULL UNIQUE NONCLUSTERED," +
                                                        "[AG_BINARY_DATA] [varbinary](max) NOT NULL" +
                                                        ")";
        private const string CreateFlowsTableSchema = "CREATE TABLE AG_FLOWS(" +
                                                        "[AG_ID] [uniqueidentifier] NOT NULL," +
                                                        "[AG_NAME] [nvarchar](50) NOT NULL UNIQUE NONCLUSTERED," +
                                                        "[AG_BINARY_DATA] [varbinary](MAX) NOT NULL" +
                                                        ")";

        #endregion

        #region Construction

        /// <summary>
        /// Default private constructor
        /// </summary>
        private DesignerDataBaseManager()
        {
        }

        #endregion

        #region Properties

        /// <summary>
        /// for getting instance of factory
        /// </summary>
        public static DesignerDataBaseManager Instance
        {
            get
            {
                if (null == instance)
                {
                    lock (syncObj)
                    {
                        if (null == instance)
                        {
                            instance = new DesignerDataBaseManager();
                        }
                    }
                }

                return instance;
            }
        }

        public static DesignerDataBaseManager GetInstance( string _ConnectionString )
        {
            if (null == instance)
            {
                lock (syncObj)
                {
                    if (null == instance)
                    {
                        instance = new DesignerDataBaseManager();            
                    }
                }
            }
            

            instance.ConnectionString = _ConnectionString;

            return instance;
        }

        public string ConnectionString
        {
            get { return _ConnectionString; }
            ///tylko na potrzeby testów, ostateczne rozwiązanie należy do Ciebie ;)
            set { _ConnectionString = value; }
        }

        #endregion

        #region Schemas Methods

        /// <summary>
        /// creates database schema for generator
        /// </summary>
        /// <returns></returns>
        private bool CreateTableSchemas(string DbName)
        {
            connection = new SqlConnection();
            connection.ConnectionString = _ConnectionString;

            //preparing create database command
            SqlCommand createDBCommad = new SqlCommand(CreateDatabaseCommand + " " + DbName, connection);

            //preparing use command
            SqlCommand useDBCommand = new SqlCommand(UseDatabaseCommand + " " + DbName, connection);

            //preparing forms table, lists table and workflows table - schema command
            SqlCommand createFormsTableCommad = new SqlCommand(CreateFormsTableSchema, connection);
            SqlCommand createListsTableCommad = new SqlCommand(CreateListsTableSchema, connection);
            SqlCommand createFlowsTableCommad = new SqlCommand(CreateFlowsTableSchema, connection);
            SqlCommand createMenuTableCommad = new SqlCommand(CreateMenuTableSchema, connection);

            try
            {
                connection.Open();

                //execute commands
                createDBCommad.ExecuteNonQuery();
                useDBCommand.ExecuteNonQuery();
                createFormsTableCommad.ExecuteNonQuery();
                createListsTableCommad.ExecuteNonQuery();
                createFlowsTableCommad.ExecuteNonQuery();
                createMenuTableCommad.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                string ssss = ex.Message;
                return false;
            }
            finally
            {
                connection.Close();
            }

            //don't need to create database anymore...
            ChangeConnectionString(ConnectionStringType.Load);

            return true;
        }

        /// <summary>
        /// Creates Connection String 
        /// </summary>
        /// <param name="DbName">database name</param>
        /// <param name="UserName">user login</param>
        /// <param name="Passwd">user password</param>
        /// <param name="t"> type of connection string </param>
        /// <returns></returns>
        public string PrepareConnectionString(string DbName, string UserName, string Passwd, ConnectionStringType t)
        {
            ConnectionStrings[0] = @"Data Source=LOCALHOST\sqlexpress;DATABASE = master;User ID=" + UserName + ";Password=" + Passwd;
            ConnectionStrings[1] = @"Data Source=LOCALHOST\sqlexpress;Initial Catalog=" + DbName + ";User ID=" + UserName + ";" + "Password=" + Passwd;

            if (ConnectionStringType.Creation == t)
            {
                _ConnectionString = ConnectionStrings[0];
            }
            else
            {
                _ConnectionString = ConnectionStrings[1];
            }

            //save state
            connectionType = t;

            return _ConnectionString;
        }

        public bool PrepareDatabase(string dbName)
        {
            //create new db only if necessary
            if (ConnectionStringType.Creation == connectionType)
            {
                return CreateTableSchemas(dbName);
            }

            return true;
        }

        private void ChangeConnectionString(ConnectionStringType csType)
        {
            if (ConnectionStringType.Creation == csType)
            {
                _ConnectionString = ConnectionStrings[0];
            }
            else
            {
                _ConnectionString = ConnectionStrings[1];
            }
        }

        #endregion

        #region Database Methods


        /// <summary>
        /// Returns 
        /// </summary>
        /// <returns></returns>
        public List<List<object>> GetVersionInfoValues(DataAccess.Policy type)
        {
            SetCommandTypeStrategy(type);

            List<List<object>> result = new List<List<object>>();

            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;
                SqlCommand Command = new SqlCommand(commandStrategy.SelectVersionInfo(), connection);
                connection.Open();

                try
                {
                    using (SqlDataReader reader = Command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            List<object> internalList = new List<object>();
                            internalList.Add(reader.GetGuid(0));
                            internalList.Add(reader.GetString(1));
                            result.Add(internalList);
                        }

                        reader.Close();
                    }

                }
                catch (Exception ex)
                {
                    string ss = ex.Message;
                    return null;
                }
                finally
                {
                    connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Saves serialized object into database
        /// </summary>
        /// <param name="binData">serialized FormControlContainer</param>
        /// <param name="id">Form Id</param>
        /// <returns>true if execution is successfull, false otherwise</returns>
        public bool SaveToDatabase(Policy type, object binData, Guid id, string title)
        {
            SetCommandTypeStrategy(type);

            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;

                SqlCommand Command = new SqlCommand(commandStrategy.InsertToTableCommand(), connection);
                connection.Open();

                Command.Parameters.AddWithValue("@id", id);
                Command.Parameters.AddWithValue("@name", title);
                Command.Parameters.AddWithValue("@binData", binData);

                try
                {
                    Command.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    return false;
                }
                finally
                {
                    connection.Close();
                }
            }

            return true;

        }

        /// <summary>
        /// Loads serialized FormControlContainer from database
        /// </summary>
        /// <param name="id">Form Id</param>
        /// <returns>serialized FormControlContainer</returns>
        public byte[] LoadFromDatabase(Policy type, Guid id)
        {

            SetCommandTypeStrategy(type);
            byte[] bytes = null;

            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;
                SqlCommand Command = new SqlCommand(commandStrategy.SelectFromTableCommand(), connection);
                connection.Open();

                Command.Parameters.AddWithValue("@id", id);

                try
                {
                    using (SqlDataReader reader = Command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            bytes = (byte[])reader["AG_BINARY_DATA"];
                        }

                        reader.Close();
                    }

                }
                catch (Exception)
                {
                    return null;
                }
                finally
                {
                    connection.Close();
                }
            }

            return bytes;
        }

        /// <summary>
        /// Loads serialized FormControlContainer from database
        /// </summary>
        /// <param name="id">Form title</param>
        /// <returns>serialized FormControlContainer</returns>
        public byte[] LoadFromDatabase(Policy type, string title)
        {
            SetCommandTypeStrategy(type);
            byte[] bytes = null;

            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;
                SqlCommand Command = new SqlCommand(commandStrategy.SelectFromTableByNameCommand(), connection);
                connection.Open();

                Command.Parameters.AddWithValue("@name", title);

                try
                {
                    using (SqlDataReader reader = Command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            bytes = (byte[])reader["AG_BINARY_DATA"];
                        }

                        reader.Close();
                    }

                }
                catch (Exception)
                {
                    return null;
                }
                finally
                {
                    connection.Close();
                }
            }

            return bytes;
        }


        /// <summary>
        /// Update form with new serialized form
        /// </summary>
        /// <param name="title">form title (id)</param>
        /// <param name="binary">serialized form</param>
        /// <returns>true if everything is ok, false - otherwise</returns>
        public bool UpdateTable(Policy type, string title, object binary)
        {
            SetCommandTypeStrategy(type);
            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;
                SqlCommand Command = new SqlCommand(commandStrategy.UpdateToTableCommand(), connection);
                connection.Open();

                Command.Parameters.AddWithValue("@bin_data", binary);
                Command.Parameters.AddWithValue("@name", title);

                try
                {
                    Command.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    return false;
                }
                finally
                {
                    connection.Close();
                }
            }

            return true;
        }

        /// <summary>
        /// Returns all names of forms in database
        /// </summary>
        /// <returns>string table of form names in database</returns>
        public List<string> GetNames(Policy type)
        {
            SetCommandTypeStrategy(type);
            List<string> result = new List<string>();

            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;

                SqlCommand Command = new SqlCommand(commandStrategy.SelectNames(), connection);
                connection.Open();

                try
                {
                    using (SqlDataReader reader = Command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            result.Add(reader.GetString(0));
                        }

                        reader.Close();
                    }

                }
                catch (Exception ex)
                {
                    string ss = ex.Message;
                    return null;
                }
                finally
                {
                    connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// returns name of selected flow/form/list
        /// </summary>
        /// <param name="id"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public string GetName(Guid id, Policy type)
        {

            SetCommandTypeStrategy(type);
            string res = null;

            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;
                SqlCommand Command = new SqlCommand(commandStrategy.SelectName(), connection);
                connection.Open();

                Command.Parameters.AddWithValue("@id", id);

                try
                {
                    using (SqlDataReader reader = Command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            res = (string)reader["AG_NAME"];
                        }

                        reader.Close();
                    }

                }
                catch (Exception ex)
                {
                    string exm = ex.Message;
                    return null;
                }
                finally
                {
                    connection.Close();
                }
            }

            return res;









            ///

            /*SetCommandTypeStrategy(type);
            string result = "";

            using (connection = new SqlConnection())
            {
                connection.ConnectionString = _ConnectionString;

                SqlCommand Command = new SqlCommand(commandStrategy.SelectName(), connection);
                Command.Parameters.AddWithValue("@id", id);

                connection.Open();

                try
                {
                    using (SqlDataReader reader = Command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        
                        result = (string)reader["AG_NAME"];
                        reader.Close();
                    }

                }
                catch (Exception ex)
                {
                    string ss = ex.Message;
                    return null;
                }
                finally
                {
                    connection.Close();
                }
            }

            return result;*/
        }

        void SetCommandTypeStrategy(Policy type)
        {
            switch (type)
            {
                case Policy.Form:
                    commandStrategy = formCommands;
                    break;
                case Policy.List:
                    commandStrategy = listCommands;
                    break;
                case Policy.Workflow:
                    commandStrategy = workflowCommands;
                    break;
                case Policy.Menu:
                    commandStrategy = menuCommands;
                    break;
            }
        }

        #endregion

    }
}
